﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebApplication1.DBBroker
{
    public class OutstandingRequisition
    {
        static LogicUniversityT2Entities logic = new LogicUniversityT2Entities();
        public OutstandingRequisition() { }
        public OutstandingRequisition(string r)
        {
            requestID = r;
        }
        public OutstandingRequisition(string id, DateTime id2, DateTime id3)
        {
            requestID = id;
            date_from = id2;
            date_to = id3;
        }
        public static string requestID
        { get; set; }
        public static DateTime date_from
        { get; set; }
        public static DateTime date_to
        { get; set; }
        public static IQueryable getOutstandingList()
        {
            string plswork = "";
            var depID = from user in logic.InventoryUsers
                        where user.EmployeeID == requestID
                        select new { dID = user.DepartmentID };
            foreach (var id in depID)
            {
                plswork = id.dID;
            }
            var olist = from empReq in logic.EmployeeRequisitions
                        join empReqDetail in logic.EmployeeRequisitionDetails on empReq.EmployeeRequisitionID equals empReqDetail.EmployeeRequisitionID
                        join emp in logic.InventoryUsers on empReq.EmployeeID equals emp.EmployeeID
                        join item in logic.Items on empReqDetail.ItemID equals item.ItemID
                        join itemDetail in logic.ItemDetails on empReqDetail.ItemID equals itemDetail.ItemID
                        join cat in logic.Categories on empReqDetail.CategoryID equals cat.CategoryID
                        where empReqDetail.AllocatedQuantity<empReqDetail.RequestedQuantity && empReq.Status.CompareTo("reject")<0 && empReq.EmployeeID.CompareTo(plswork)>0
                        let ReqID = empReq.EmployeeRequisitionID
                        let ReqDate = empReq.EmployeeRequisitionDate
                        let EmpName = emp.Name
                        let TotalAmount = empReq.TotalAmount
                        let ItemDes = item.ItemDescription
                        let Category = cat.CategoryName
                        let Requested_Qty = empReqDetail.RequestedQuantity
                        let Allocated_Qty = empReqDetail.AllocatedQuantity
                        let Outstanding_Qty = empReqDetail.RequestedQuantity - empReqDetail.AllocatedQuantity
                        group itemDetail by
                        new
                        {
                            ReqID,
                            ReqDate,
                            EmpName,
                            TotalAmount,
                            ItemDes,
                            Category,
                            Requested_Qty,
                            Allocated_Qty,
                            Outstanding_Qty,
                            //avgPrice=itemDetail.Price
                        } into grouped
                        select
                        new
                        {
                            grouped.Key.ReqID,//ReqID=empReq.EmployeeRequisitionID,
                            grouped.Key.ReqDate,//ReqDate=empReq.EmployeeRequisitionDate,
                            grouped.Key.EmpName,//EmpName=emp.Name,
                            grouped.Key.TotalAmount,// TotalAmount=empReq.TotalAmount,
                            grouped.Key.ItemDes,//ItemDes=item.ItemDescription,
                            grouped.Key.Category,//Category=cat.CategoryName,
                            grouped.Key.Requested_Qty,//Requested_Qty=empReqDetail.RequestedQuantity,
                            grouped.Key.Allocated_Qty,//Allocated_Qty=empReqDetail.AllocatedQuantity,
                            grouped.Key.Outstanding_Qty,//Outstanding_Qty=empReqDetail.RequestedQuantity-empReqDetail.AllocatedQuantity,
                            //Price=itemDetail.Price
                            //AveragePrice = grouped.Average(p => p.Price)

                        };
            return olist;

        }

        public static IQueryable getOutstandingDetailList()
        {
            var odlist = from empReq in logic.EmployeeRequisitions
                         join empReqDetail in logic.EmployeeRequisitionDetails on empReq.EmployeeRequisitionID equals empReqDetail.EmployeeRequisitionID
                         join emp in logic.InventoryUsers on empReq.EmployeeID equals emp.EmployeeID
                         join item in logic.Items on empReqDetail.ItemID equals item.ItemID
                         join itemDetail in logic.ItemDetails on empReqDetail.ItemID equals itemDetail.ItemID
                         join cat in logic.Categories on empReqDetail.CategoryID equals cat.CategoryID
                         where empReqDetail.EmployeeRequisitionID == requestID && empReqDetail.AllocatedQuantity < empReqDetail.RequestedQuantity
                         let ReqID = empReq.EmployeeRequisitionID
                         let ReqDate = empReq.EmployeeRequisitionDate
                         let EmpName = emp.Name
                         let TotalAmount = empReq.TotalAmount
                         let ItemDes = item.ItemDescription
                         let Category = cat.CategoryName
                         let Requested_Qty = empReqDetail.RequestedQuantity
                         let Allocated_Qty = empReqDetail.AllocatedQuantity
                         let Outstanding_Qty = empReqDetail.RequestedQuantity - empReqDetail.AllocatedQuantity
                         group itemDetail by
                         new
                         {
                             ReqID,
                             ReqDate,
                             EmpName,
                             TotalAmount,
                             ItemDes,
                             Category,
                             Requested_Qty,
                             Allocated_Qty,
                             Outstanding_Qty,

                         } into grouped
                         select
                         new
                         {
                             grouped.Key.ReqID,
                             grouped.Key.ReqDate,
                             grouped.Key.EmpName,
                             grouped.Key.TotalAmount,
                             grouped.Key.ItemDes,
                             grouped.Key.Category,//Category=cat.CategoryName,
                             grouped.Key.Requested_Qty,//Requested_Qty=empReqDetail.RequestedQuantity,
                             grouped.Key.Allocated_Qty,//Allocated_Qty=empReqDetail.AllocatedQuantity,
                             grouped.Key.Outstanding_Qty,//Outstanding_Qty=empReqDetail.RequestedQuantity-empReqDetail.AllocatedQuantity,
                             Price = (grouped.Average(p => p.Price)) * grouped.Key.Outstanding_Qty
                             //(int)grpProdutosFornecedores.Max(item => item.ID_TABELANCMS)
                         };
            return odlist;
        }
        public static IQueryable getDistributionList()
        {
            string plswork = "";
            var depID = from user in logic.InventoryUsers
                        where user.EmployeeID == requestID
                        select new { dID = user.DepartmentID };
            foreach (var id in depID)
            {
                plswork = id.dID;
            }
            //DateTime dt = Convert.ToDateTime(logic.DepartmentRequisitions.Max(g => g.AllocatedDate));
            var odlist = from empReq in logic.EmployeeRequisitions
                         join empReqDetail in logic.EmployeeRequisitionDetails on empReq.EmployeeRequisitionID equals empReqDetail.EmployeeRequisitionID
                         join emp in logic.InventoryUsers on empReq.EmployeeID equals emp.EmployeeID
                         join item in logic.Items on empReqDetail.ItemID equals item.ItemID
                         join itemDetail in logic.ItemDetails on empReqDetail.ItemID equals itemDetail.ItemID
                         join cat in logic.Categories on empReqDetail.CategoryID equals cat.CategoryID
                         where empReqDetail.AllocatedQuantity != 0 && (empReq.Status == "sent") && empReq.DepartmentID == plswork
                         let ReqID = empReq.EmployeeRequisitionID
                         let ReqDate = empReq.EmployeeRequisitionDate
                         let EmpName = emp.Name
                         let TotalAmount = empReq.TotalAmount
                         let ItemDes = item.ItemDescription
                         let Category = cat.CategoryName
                         let Requested_Qty = empReqDetail.RequestedQuantity
                         let Allocated_Qty = empReqDetail.AllocatedQuantity
                         let Outstanding_Qty = empReqDetail.RequestedQuantity - empReqDetail.AllocatedQuantity
                         group itemDetail by
                         new
                         {
                             ReqID,
                             ReqDate,
                             EmpName,
                             TotalAmount,
                             ItemDes,
                             Category,
                             Requested_Qty,
                             Allocated_Qty,
                             Outstanding_Qty,

                         } into grouped
                         select
                         new
                         {
                             grouped.Key.ReqID,
                             grouped.Key.ReqDate,
                             grouped.Key.EmpName,
                             grouped.Key.TotalAmount,
                             grouped.Key.ItemDes,
                             grouped.Key.Category,
                             grouped.Key.Requested_Qty,
                             grouped.Key.Allocated_Qty,
                             grouped.Key.Outstanding_Qty,
                             Price = (grouped.Average(p => p.Price)) * grouped.Key.Outstanding_Qty
                             //(int)grpProdutosFornecedores.Max(item => item.ID_TABELANCMS)
                         };
            return odlist;
        }
        public static IQueryable getDistributionByPeriodList()
        {
            string plswork = "";
            var depID = from user in logic.InventoryUsers
                        where user.EmployeeID == requestID
                        select new { dID = user.DepartmentID };
            foreach (var id in depID)
            {
                plswork = id.dID;
            }
            //DateTime dt = Convert.ToDateTime(logic.DepartmentRequisitions.Max(g => g.AllocatedDate));FormattedDate = d.ReportDate.ToString("yyyy-MMM")
            var odlist = from empReq in logic.EmployeeRequisitions
                         join empReqDetail in logic.EmployeeRequisitionDetails on empReq.EmployeeRequisitionID equals empReqDetail.EmployeeRequisitionID
                         join emp in logic.InventoryUsers on empReq.EmployeeID equals emp.EmployeeID
                         join item in logic.Items on empReqDetail.ItemID equals item.ItemID
                         join itemDetail in logic.ItemDetails on empReqDetail.ItemID equals itemDetail.ItemID
                         join cat in logic.Categories on empReqDetail.CategoryID equals cat.CategoryID
                         where empReqDetail.AllocatedQuantity != 0 && (empReq.EmployeeRequisitionDate >=date_from && empReq.EmployeeRequisitionDate<=date_to) && (empReq.Status == "sent") && emp.DepartmentID == plswork
                         let ReqID = empReq.EmployeeRequisitionID
                         let ReqDate = empReq.EmployeeRequisitionDate
                         let EmpName = emp.Name
                         let TotalAmount = empReq.TotalAmount
                         let ItemDes = item.ItemDescription
                         let Category = cat.CategoryName
                         let Requested_Qty = empReqDetail.RequestedQuantity
                         let Allocated_Qty = empReqDetail.AllocatedQuantity
                         let Outstanding_Qty = empReqDetail.RequestedQuantity - empReqDetail.AllocatedQuantity
                         group itemDetail by
                         new
                         {
                             ReqID,
                             ReqDate,
                             EmpName,
                             TotalAmount,
                             ItemDes,
                             Category,
                             Requested_Qty,
                             Allocated_Qty,
                             Outstanding_Qty,

                         } into grouped
                         select
                         new
                         {
                             grouped.Key.ReqID,
                             grouped.Key.ReqDate,
                             grouped.Key.EmpName,
                             grouped.Key.TotalAmount,
                             grouped.Key.ItemDes,
                             grouped.Key.Category,
                             grouped.Key.Requested_Qty,
                             grouped.Key.Allocated_Qty,
                             grouped.Key.Outstanding_Qty,
                             Price = (grouped.Average(p => p.Price)) * grouped.Key.Outstanding_Qty
                             //(int)grpProdutosFornecedores.Max(item => item.ID_TABELANCMS)
                         };
            return odlist;
        }

        public static IQueryable getDistributionDetailList()
        {
            var odlist = from empReq in logic.EmployeeRequisitions
                         join empReqDetail in logic.EmployeeRequisitionDetails on empReq.EmployeeRequisitionID equals empReqDetail.EmployeeRequisitionID
                         join emp in logic.InventoryUsers on empReq.EmployeeID equals emp.EmployeeID
                         join item in logic.Items on empReqDetail.ItemID equals item.ItemID
                         join itemDetail in logic.ItemDetails on empReqDetail.ItemID equals itemDetail.ItemID
                         join cat in logic.Categories on empReqDetail.CategoryID equals cat.CategoryID
                         where empReqDetail.EmployeeRequisitionID == requestID && (empReq.Status == "sent") && empReqDetail.AllocatedQuantity != 0
                         let ReqID = empReq.EmployeeRequisitionID
                         let ReqDate = empReq.EmployeeRequisitionDate
                         let EmpName = emp.Name
                         let TotalAmount = empReq.TotalAmount
                         let ItemDes = item.ItemDescription
                         let Category = cat.CategoryName
                         let Requested_Qty = empReqDetail.RequestedQuantity
                         let Allocated_Qty = empReqDetail.AllocatedQuantity
                         let Outstanding_Qty = empReqDetail.RequestedQuantity - empReqDetail.AllocatedQuantity
                         group itemDetail by
                         new
                         {
                             ReqID,
                             ReqDate,
                             EmpName,
                             TotalAmount,
                             ItemDes,
                             Category,
                             Requested_Qty,
                             Allocated_Qty,
                             Outstanding_Qty,

                         } into grouped
                         select
                         new
                         {
                             grouped.Key.ReqID,
                             grouped.Key.ReqDate,
                             grouped.Key.EmpName,
                             grouped.Key.TotalAmount,
                             grouped.Key.ItemDes,
                             grouped.Key.Category,//Category=cat.CategoryName,
                             grouped.Key.Requested_Qty,//Requested_Qty=empReqDetail.RequestedQuantity,
                             grouped.Key.Allocated_Qty,//Allocated_Qty=empReqDetail.AllocatedQuantity,
                             grouped.Key.Outstanding_Qty,//Outstanding_Qty=empReqDetail.RequestedQuantity-empReqDetail.AllocatedQuantity,
                             Price = (grouped.Average(p => p.Price)) * grouped.Key.Outstanding_Qty
                             //(int)grpProdutosFornecedores.Max(item => item.ID_TABELANCMS)
                         };
            return odlist;
        }
    }
}